﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Text;
using System.Web;
using NPOI.HSSF.UserModel; //xls格式(97-2003)
using NPOI.XSSF.UserModel; //xlsx格式(2007)
using NPOI.SS.UserModel;

namespace Tzx.Utilities.Npoi
{
    /// <summary>
    /// Npoi导出辅助类
    /// </summary>
    public class NpoiExportListT<V>
    {

        #region 构造

        /// <summary>
        /// Npoi导出辅助类(构造)
        /// </summary>
        /// <param name="listT"></param>
        /// <param name="isExcel2007">是否为Excel2007格式</param>
        public NpoiExportListT(List<V> listT, bool isExcel2007)
        {
            if (listT == null)
            {
                throw new ApplicationException("源对象集合不能为空");
            }

            //
            this._sourceListT = listT;
            this._isExcel2007 = isExcel2007;
        }

        /// <summary>
        /// 构造(默认导出Excel97-2003老格式,兼容)
        /// </summary>
        /// <param name="listT">待导出的ListT集合</param>
        public NpoiExportListT(List<V> listT) : this(listT, false)
        {
            //
        }

        /// <summary>
        /// 源数据实体列表
        /// </summary>
        public List<V> SourceListT
        {
            get { return _sourceListT; }
        }
        private List<V> _sourceListT;

        /// <summary>
        /// 是否为Excel2007格式
        /// </summary>
        protected bool IsExcel2007
        {
            get { return _isExcel2007; }
        }
        private bool _isExcel2007 = false;

        /// <summary>
        /// 属性名
        /// </summary>
        protected string[] SourceTProperty
        {
            get
            {
                if (_sourceTProperty == null)
                {
                    List<string> list = new List<string>();
                    //反射属性名
                    foreach (PropertyInfo info in typeof(V).GetProperties())
                    {
                        //TODO:判断基础数据类型
                        list.Add(info.Name);
                    }
                    _sourceTProperty = list.ToArray();
                }
                return _sourceTProperty;
            }
        }
        private string[] _sourceTProperty;

        /// <summary>
        /// 是否存在此属性名
        /// </summary>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        protected bool ExistSourceTProperty(string propertyName)
        {
            int index = Array.FindIndex(SourceTProperty,
                delegate(string s)
                {
                    return String.Compare(s, propertyName, StringComparison.OrdinalIgnoreCase) == 0;
                });
            return index > -1;
        }

        #endregion

        #region 导出参数

        /// <summary>
        /// 设置列标题
        /// </summary>
        public void SetColumnCaption(string colName, string colCaption)
        {
            if (!ExistSourceTProperty(colName))
            {
                return;
            }
            if (ColumnCaption.ContainsKey(colName))
            {
                ColumnCaption[colName] = colCaption;
            }
            else
            {
                ColumnCaption.Add(colName, colCaption);
            }
        }

        /// <summary>
        /// 列标题
        /// </summary>
        public Dictionary<string, string> ColumnCaption
        {
            get
            {
                if (_columnCaption == null)
                {
                    _columnCaption = new Dictionary<string, string>();
                }
                return _columnCaption;
            }
        }
        private Dictionary<string, string> _columnCaption;

        /// <summary>
        /// 只导出这些列名的列
        /// </summary>
        public string[] OnlyColumnNames
        {
            get
            {
                if (_onlyColumnNames == null)
                {
                    _onlyColumnNames = new string[0];
                }
                return _onlyColumnNames;
            }
            set { _onlyColumnNames = value; }
        }
        private string[] _onlyColumnNames;

        /// <summary>
        /// 图片列的列名(byte[]类型)
        /// </summary>
        public string[] ImageColumnNames
        {
            get
            {
                if (_imageColumnNames == null)
                {
                    _imageColumnNames = new string[0];
                }
                return _imageColumnNames;
            }
            set { _imageColumnNames = value; }
        }
        private string[] _imageColumnNames;

        /// <summary>
        /// 将列排序(设置ListT列的导出顺序)
        /// </summary>
        public bool SortListColumn(string[] newColNameSs)
        {
            if (SourceListT == null)
            {
                return false;
            }

            if (newColNameSs == null || newColNameSs.Length == 0)
            {
                return false;
            }

            //
            List<string> colSortList = new List<string>(); //需要排序(++)
            List<string> colNotSortList = new List<string>(SourceTProperty); //不在排序列表中(--)
            foreach (string colSort in newColNameSs)
            {
                string sort = colSort;
                int existIndex =
                    Array.FindIndex(SourceTProperty,
                        delegate(string s)
                        {
                            return String.Compare(s, sort, StringComparison.OrdinalIgnoreCase) == 0;
                        });
                if (existIndex > -1)
                {
                    //排序列表
                    colSortList.Add(colSort);
                }
                else
                {
                    //不排序的列表
                    colNotSortList.Remove(colSort);
                }
            }


            //替换成新顺序
            List<string> list = new List<string>(colSortList);
            list.AddRange(colNotSortList);
            _sourceTProperty = list.ToArray();//换成新的列表

            //
            return true;
        }
        
        /// <summary>
        /// 导出Excel的行高(统一行高)
        /// </summary>
        public short? ExcelRowHeight { get; set; }

        /// <summary>
        /// 导出Excel的列宽(列名_列宽)
        /// </summary>
        public Dictionary<string, int> ExcelColumnWidth
        {
            get
            {
                if (_excelColumnWidth == null)
                {
                    _excelColumnWidth = new Dictionary<string, int>();
                }
                return _excelColumnWidth;
            }
            set { _excelColumnWidth = value; }
        }
        private Dictionary<string, int> _excelColumnWidth;

        /// <summary>
        /// 设置导出模板表
        /// </summary>
        /// <param name="templateExcelStream"></param>
        public void SetTemplateExcel(Stream templateExcelStream)
        {
            //读取原Excel文件
            //_templateWorkbook = new HSSFWorkbook(templateExcelStream);
            if (!IsExcel2007)
            {
                _templateWorkbook = new HSSFWorkbook(templateExcelStream);
            }
            else
            {
                _templateWorkbook = new XSSFWorkbook(templateExcelStream);
            }

            //
            ISheet sheet = null;
            if (_templateWorkbook.NumberOfSheets > 0)
            {
                sheet = _templateWorkbook.GetSheetAt(0);
            }
            if (sheet == null)
            {
                return;//模板不正确,无格式导出
            }

            hasTemplate = true;//有导出模板

            //模板行样式
            ICellStyle styleRow;
            Dictionary<int, ICellStyle> styleColumns;
            //bool styleOk = GetStyle(sheet, 1, 0, 0 + SourceListT.SourcePropertyCaption.Count, out styleRow, out styleColumns);
            bool styleOk = NpoiUtilities.GetStyle(sheet, 1, 0, 0 + SourceTProperty.Length, out styleRow, out styleColumns);

            //插入行
            //int rows = SourceListT.Rows.Count;
            int rows = SourceListT.Count;
            sheet.ShiftRows(1, rows + 1, rows, true, true);

            //设置样式
            if (styleOk)
            {
                for (int iRow = 1; iRow <= rows; iRow++)
                {
                    IRow excelRow = sheet.GetRow(iRow);
                    if (excelRow == null)
                    {
                        excelRow = sheet.CreateRow(iRow);
                    }

                    //
                    if (styleRow != null)
                    {
                        excelRow.RowStyle = styleRow;
                    }
                    for (int iCol = 0; iCol <= SourceTProperty.Length; iCol++)
                    {
                        ICellStyle cellStyle = styleColumns[iCol];
                        ICell cell = null;
                        if (excelRow.Cells.Count > 0)
                        {
                            cell = excelRow.GetCell(iCol);
                        }
                        if (cell == null)
                        {
                            cell = excelRow.CreateCell(iCol);
                        }
                        if (cellStyle != null)
                        {
                            cell.CellStyle = cellStyle;
                        }
                    }
                }
            }

            //

        }

        /// <summary>
        /// 模板工作簿
        /// </summary>
        private IWorkbook _templateWorkbook;

        /// <summary>
        /// 是否按模板导出
        /// </summary>
        private bool hasTemplate = false;

        #endregion

        /// <summary>
        /// 导出(ListT => Excel流)
        /// </summary>
        /// <returns></returns>
        public MemoryStream ListT2ExcelStream()
        {
            if (SourceListT == null) return null;

            //判断是无格式导出,还是按模板导出
            IWorkbook workbook;
            if (hasTemplate)
            {
                workbook = _templateWorkbook;
            }
            else
            {
                //workbook = new HSSFWorkbook();
                if (!IsExcel2007)
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = new XSSFWorkbook();
                }
            }

            //导出
            bool ok = ListT2Excel(ref workbook);
            if (!ok)
            {
                return null;
            }

            #region stream无法访问已关闭的流
            //MemoryStream ms = new MemoryStream();
            //workbook.Write(ms);
            //ms.Flush();
            //ms.Position = 0;//BUG:无法访问已关闭的流。
            //workbook = null;

            NpoiMemoryStream ms = new NpoiMemoryStream();
            ms.AllowClose = false;
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            workbook = null;
            ms.AllowClose = true;
            #endregion

            return ms;
        }

        /// <summary>
        /// 导出Excel(DataTable => Excel对象)
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <returns></returns>
        private bool ListT2Excel(ref IWorkbook workbook)
        {
            if (SourceListT == null) return false;
            if (workbook == null) return false;

            //
            //IWorkbook workbook = new HSSFWorkbook();
            //MemoryStream ms = new MemoryStream();

            //工作表
            ISheet sheet = null;
            if (workbook.NumberOfSheets > 0)
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet == null)
            {
                sheet = workbook.CreateSheet();
            }

            //工作表打印设置
            //templateExcelSheet.PrintSetup.Landscape = true;//横向
            //templateExcelSheet.PrintSetup.NoOrientation
            //templateExcelSheet.PrintSetup.Notes
            //templateExcelSheet.PrintSetup.UsePage
            sheet.PrintSetup.FitWidth = sheet.PrintSetup.FitHeight = 0; //使固定宽度和高度无效
            sheet.PrintSetup.Scale = 100; //缩放比例100%
            sheet.PrintSetup.PaperSize = 9; //9; //PaperSize.A4
            //templateExcelSheet.PrintSetup.ValidSeVings = true;

            //标题行(列名,标题)
            //Dictionary<string, string> headerColumnNameCaption = ListT2ExcelHeader(sheet);
            ListT2ExcelHeader(sheet);

            // handling value.
            int rowIndex = 1;

            //常规格式
            ICellStyle styleGeneral = workbook.CreateCellStyle();
            styleGeneral.DataFormat = 0;//"General";

            //日期样式
            ICellStyle styleDate = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            styleDate.DataFormat = format.GetFormat("yyyy-mm-dd");
            //int styleCount = workbook.NumCellStyles;

            //创建一个"绘画器",这个绘画器用于所有的图片写入
            //请注意,是所有的图片,不可一张图片创建一个!否则将导致没有图片
            //HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            //HSSFPatriarch patriarch = templateExcelSheet.CreateDrawingPatriarch();

            #region xls97还是xlsx2007,CreatePicture

            HSSFPatriarch patriarch97 = null;
            XSSFDrawing patriarch2007 = null;
            if (!IsExcel2007)
            {
                patriarch97 = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //是Excel97_2003
            }
            else
            {
                patriarch2007 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); //是Excel2007
            }

            #endregion

            //string[] onlyColumnName = OnlyColumnNames.ToArray();

            //导出行
            foreach (V t in SourceListT)
            {
                //IRow dataRow = sheet.CreateRow(rowIndex);
                IRow dataRow = sheet.GetRow(rowIndex);
                if (dataRow == null)
                {
                    dataRow = sheet.CreateRow(rowIndex);
                }

                //行高
                if (ExcelRowHeight.HasValue)
                {
                    dataRow.Height = ExcelRowHeight.Value; //高度
                }

                //
                int exportColumnIndex2 = 0; //列Index,而不是直接用column.Ordinal,因为有选择导出列的情况
                //foreach (DataColumn column in SourceListT.SourcePropertyCaption)
                foreach (PropertyInfo info in typeof (V).GetProperties())
                {
                    ICell dataCell = dataRow.GetCell(exportColumnIndex2);
                    if (dataCell == null)
                    {
                        dataCell = dataRow.CreateCell(exportColumnIndex2);
                    }

                    //只导出指定列
                    if (OnlyColumnNames != null && OnlyColumnNames.Length > 0)
                    {
                        //bool inColumnName = Array.IndexOf(onlyColumnName, column.ColumnName) != -1; //Key
                        bool inColumnName = Array.IndexOf(OnlyColumnNames, info.Name) != -1; //Key
                        if (!inColumnName)
                        {
                            continue; //跳出不导
                        }
                    }

                    //dataCell.CellStyle = workbook.GetCellStyleAt(0);//格式

                    //NULL值
                    if (info == null)
                    {
                        dataCell.SetCellValue(string.Empty);
                        exportColumnIndex2 += 1; //导出列Index
                        continue; //跳出
                    }

                    //NULL
                    object o = info.GetValue(t, null);
                    if (o == null)
                    {
                        dataCell.SetCellValue(string.Empty);
                        exportColumnIndex2 += 1; //导出列Index
                        continue; //跳出
                    }

                    //是否为用户指定的图片列
                    bool isImageCol = (Array.IndexOf(ImageColumnNames, info.Name) > -1);
                    Type[] integerArray = { typeof(Int16), typeof(Int32), typeof(Int64), typeof(Byte) };
                    Type[] decimalsArray = { typeof(Decimal), typeof(Double), typeof(Single) };

                    //导出

                    //1.数字型(整数)
                    if (Array.IndexOf(integerArray, info.PropertyType) != -1)
                    {
                        int i = 0;
                        int.TryParse(o.ToString(), out i);
                        dataCell.SetCellValue(i);
                        //dataCell.CellStyle.DataFormat = format.GetFormat("0");
                        if (!hasTemplate)
                        {
                            dataCell.CellStyle = styleGeneral;//格式化显示
                        }
                    }
                    //2.小数型
                    else if (Array.IndexOf(decimalsArray, info.PropertyType) != -1)
                    {
                        decimal d = 0.00m;
                        Decimal.TryParse(o.ToString(), out d);
                        dataCell.SetCellValue((double)d);
                        if (!hasTemplate)
                        {
                            dataCell.CellStyle = styleGeneral;//格式化显示
                        }
                    }
                    //3.日期型
                    else if (info.PropertyType == typeof(DateTime))
                    {
                        //HSSFCell newCell = dataRow.CreateCell(column.Ordinal);
                        //ICell newCell = dataRow.CreateCell(exportColumnIndex2);
                        DateTime dateV;
                        if (DateTime.TryParse(o.ToString(), out dateV))
                        {
                            dataCell.SetCellValue(dateV);
                            if (!hasTemplate)
                            {
                                dataCell.CellStyle = styleDate; //格式化显示
                            }
                        }
                        else
                        {
                            dataCell.SetCellValue(string.Empty);
                        }
                    }
                    //4.图片(byte[])
                    else if (info.PropertyType == typeof(byte[]) && isImageCol)
                    {
                        byte[] bb;
                        try
                        {
                            bb = (byte[]) o;
                        }
                        catch
                        {
                            //eat
                            bb = new byte[] {};
                        }

                        //插入图片到 Excel，并返回一个图片的标识
                        int pictureIndex = workbook.AddPicture(bb, PictureType.JPEG);

                        #region xls97还是xlsx2007

                        HSSFClientAnchor anchor97 = null;
                        XSSFClientAnchor anchor2007 = null;

                        //创建图片的位置
                        if (!IsExcel2007)
                        {
                            anchor97 = new HSSFClientAnchor(
                                8, 2, //上左 到 上右 的位置,是基于下面的行列位置82
                                0, 0, //下左 到 下右 的位置,是基于下面的行列位置00
                                exportColumnIndex2, rowIndex,
                                exportColumnIndex2 + 1, rowIndex + 1
                                );
                        }
                        else
                        {
                            anchor2007 = new XSSFClientAnchor(
                                8, 2,
                                0, 0,
                                exportColumnIndex2, rowIndex,
                                exportColumnIndex2 + 1, rowIndex + 1
                                );
                        }

                        #endregion

                        #region xls97还是xlsx2007
                        //图片输出的位置这么计算的:
                        //假设我们要将图片放置于第 5(E) 列的第 2 行 
                        //对应索引为是 4 : 1(默认位置)
                        //放置的位置就等于(默认位置)到(默认位置各自加上一行,一列)
                        if (!IsExcel2007)
                        {
                            patriarch97.CreatePicture(anchor97, pictureIndex); //使用绘画器绘画图片
                        }
                        else
                        {
                            //Excel2007
                            IPicture picture = patriarch2007.CreatePicture(anchor2007, pictureIndex);
                            picture.Resize(1.05, 1.10); //
                        }

                        #endregion

                        dataCell.SetCellValue(""); //设置一个空白,以免前列溢出到本列
                    }
                    else
                    {
                        //5.其它
                        dataCell.SetCellValue(o.ToString());
                        if (!hasTemplate)
                        {
                            dataCell.CellStyle = styleGeneral;//格式化显示
                        }
                    }

                    //格式
                    //cell.RowStyle = styleAll; 

                    //
                    exportColumnIndex2 += 1; //导出列Index
                } //foreach 列

                rowIndex++;
            } //foreach行

            //workbook.Write(ms);
            //ms.Flush();
            //ms.Position = 0;

            //headerRow = null;
            //sheet = null;     //要返回,不能清除
            //workbook = null;  //要返回,不能清除

            //return ms;
            return true;
        }

        /// <summary>
        /// 导出Excel标题行(ListT => Excel对象)
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <returns></returns>
        private void ListT2ExcelHeader(ISheet sheet)
        {
            //标题行
            IRow headerRow;
            if (sheet.GetRow(0) == null)
            {
                headerRow = sheet.CreateRow(0);
            }
            else
            {
                headerRow = sheet.GetRow(0);
            }

            //标题行(列名,标题)
            //Dictionary<string, string> headerColumnNameCaption = new Dictionary<string, string>();

            //foreach (DataColumn column in SourceListT.SourcePropertyCaption)
            //foreach (PropertyInfo info in typeof(V).GetProperties())
            //{
            //    string colName = string.Format("{0}", info.Name).Trim();
            //    string colCaption = string.Format("{0}", info.Name).Trim();
            //    string caption = (String.Compare(colName, colCaption, StringComparison.InvariantCulture) == 0)
            //        ? colName
            //        : colCaption;

            //    headerColumnNameCaption.Add(colName, caption);
            //}

            //
            int exportColumnIndex = 0; //列Index,而不是直接用column.Ordinal,因为有选择导出列的情况
            foreach (string col in SourceTProperty)
            {
                //只导出指定列(标题)
                if (OnlyColumnNames != null && OnlyColumnNames.Length > 0)
                {
                    bool inColumnName = Array.IndexOf(OnlyColumnNames, col) != -1;
                    if (!inColumnName)
                    {
                        continue; //导出列不存在(列名和标题)
                    }
                }

                //导出
                string excelCaption = ColumnCaption.ContainsKey(col) ? ColumnCaption[col] : col; //Excel标题行

                //设置标题Text
                ICell cell;
                if (headerRow.GetCell(exportColumnIndex) == null)
                {
                    cell = headerRow.CreateCell(exportColumnIndex);
                }
                else
                {
                    cell = headerRow.GetCell(exportColumnIndex);
                }
                cell.SetCellValue(excelCaption);

                //列宽
                if (ExcelColumnWidth.Count > 0)
                {
                    //按列名
                    string colName = col;
                    bool isColName = ExcelColumnWidth.ContainsKey(colName);
                    if (isColName)
                    {
                        sheet.SetColumnWidth(exportColumnIndex, ExcelColumnWidth[colName]); //宽度
                    }
                    else
                    {
                        //列标题
                        if (ColumnCaption.ContainsKey(col))
                        {
                            string colCaption = ColumnCaption[col];
                            bool isColCaption = ExcelColumnWidth.ContainsKey(colCaption);
                            if (isColCaption)
                            {
                                sheet.SetColumnWidth(exportColumnIndex, ExcelColumnWidth[colName]); //宽度
                            }
                        }
                    }
                }//列宽

                //
                exportColumnIndex += 1;
            }//foreach

            //
        }

        #region 导出

        /// <summary>
        /// 保存输出到本机硬盘文件
        /// </summary>
        /// <param name="saveFileName"></param>
        public void ExportToWin(string saveFileName)
        {
            MemoryStream ms = ListT2ExcelStream();
            if (ms == null)
            {
                return;
            }

            //
            NpoiUtilities.ExportToWin(saveFileName, ms);
        }

        /// <summary>
        /// Web导出
        /// </summary>
        /// <param name="downloadFileName">文件名</param>
        public void ExportToWeb(string downloadFileName)
        {
            HttpContext curContext = HttpContext.Current;
            MemoryStream ms = ListT2ExcelStream();
            ExportToWeb(downloadFileName, ms, curContext);
        }

        /// <summary>
        /// Web导出:保存Excel流到Web的Response
        /// </summary>
        /// <param name="downloadFileName">输出文件名</param>
        /// <param name="ms">内存流</param>
        /// <param name="curContext">当前HVp上下文</param>
        public void ExportToWeb(string downloadFileName, MemoryStream ms, HttpContext curContext)
        {
            //设置编码和附件格式  
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition","aVachment;filename=" + HttpUtility.UrlEncode(downloadFileName, Encoding.UTF8));

            if (ms != null)
            {
                //GetBuffer()导致Excel2007格式文件"发现...中的部分内容有问题",改用ToArray
                //curContext.Response.BinaryWrite(ms.GetBuffer());
                curContext.Response.BinaryWrite(ms.ToArray());
            }
            curContext.Response.End();
        }

        #endregion

    }
}